import openpyxl
from anhui import *
from openpyxl.styles import PatternFill
import time


def szyqstart():
    try:
        workbook = openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(1, 2):
            title = ws.cell(row=i, column=2).value
            url = ws.cell(row=i, column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = '埇桥区信息公开'
            link['url'] = ''
            links.append(link)
            if (url != None and url.startswith('http://')):
                # chro = common(title, url)
                chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)

        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")

def start(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = common(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")
def startsixian(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = sixian(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")

def startquanjiao(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = quanjiao(title,url)
                # chro = ('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")

def starttunxi(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = tunxi(title,url)
                # chro = ('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
            # print(links)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xl
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")





def startmingguang(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = mingguang(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")


def startyingzhou(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = yingzhou(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")

def starttype2(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = type2(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")


def starttype3(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = type3(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")


def starttype1(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = type1(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")


def startgaoxin(start,end):
    try:
        workbook=openpyxl.load_workbook("source.xlsx")
        ws = workbook.get_sheet_by_name(workbook.get_sheet_names()[0])
        links = []
        for i in range(start,end):
            title = ws.cell(row=i,column=2).value
            url = ws.cell(row=i,column=3).value
            link = {}
            link['surl'] = ''
            link['title'] = title
            link['url'] =  ''
            links.append(link)
            if (url!=None and url.startswith('http://')):
                chro = gaoxin(title,url)
                # chro = szyq('埇桥区信息公开','http://www.szyq.gov.cn/public/index.html',11)
                result = chro.do()
                links.extend(result)
            link = {}
            link['surl'] = ''
            link['title'] = ''
            link['url'] = ''
            links.append(link)
    except:
        for link in links:
            print("err:")
            print(link)
    finally:
        # 创建xlsx
        wb = openpyxl.Workbook()
        # 创建sheet
        ws = wb.create_sheet('result', 0)
        i = 1
        for link in links:
            if (link['surl'] == '' and link['url'] == '' and link['title'] != ''):
                ws.merge_cells('A' + str(i) + ':C' + str(i))
                ws.cell(i, 1).value = link['title']
                ws.cell(i, 1).fill = PatternFill(fill_type='solid', fgColor="FFFF00")
            else:
                ws.cell(i, 1).value = link['surl']
                ws.cell(i, 2).value = link['title']
                ws.cell(i, 3).value = link['url']
            i = i + 1
        wb.save("result.xlsx")



s=161
e=172


stime=time.time()
#大众版  组配、主题
# start(s,e)
#埇桥
# szyqstart()
#泗县  组配、主题、体裁
# startsixian(s,e)
#全椒
# startquanjiao(s,e)
#屯溪 徽州
# starttunxi(s,e)
#明光
# startmingguang(s,e)
#颍州
# startyingzhou(s,e)

# starttype2(s,e)
# starttype3(s,e)
# starttype1(s,e)
startgaoxin(s,e)
etime=time.time()
print('时间:'+str(etime-stime)+"S")